﻿const mysql = require('mysql');
const config = require('../bin/config');

//连接数据库

var pool = mysql.createPool({
    host: config.host,
    user: config.user,
    password: config.password,
    database: config.database,
    port: config.port,
    multipleStatements: true  //是否允许执行多条sql语句
})



function select(sql, params = false, tableName) {
    return new Promise(function (reslove, reject) {

        var data = {}
        pool.getConnection(function (err, connection) {
            if (err) {

                reject(err)
                return;
            }
            console.log(sql);
            if (tableName&&tableName!=='') {
                connection.query('select count(*) from ' + tableName, (error, res) => {
                    if (error) {
                        console.error(error);
                        return
                    }
                    data.total = res[0]['count(*)']
                })

            }
            connection.query(sql, params, function (error, res) {
                connection.release()
                if (error) {
                    reject(error)
                    return;
                }
                data.data = res
                reslove(data)
            })
        })
    })
}



function SqlUtils() {
    if (!(this instanceof SqlUtils)) {
        return new SqlUtils();
    }
    this.tableName = ''

    this.add = function (tableName) {
        SqlUtils.prototype.sqlStr = 'insert into ' + tableName
        return this
    }
    this.delete = function (tableName) {
        SqlUtils.prototype.sqlStr = 'delete from ' + tableName
        return this
    }
    this.update = function (tableName) {
        SqlUtils.prototype.sqlStr = 'UPDATE ' + tableName
        return this
    }
    this.query = function (tableName, ...keys) {
        this.doneWhere =  'q'
        var str = ''
        this.tableName = tableName
        if (keys.length != 0 ) {
            for (const key in keys) {
                str += keys[key] + ','
            }
            str = str.substring(0, str.length - 1)
            SqlUtils.prototype.sqlStr = 'select ' + str + ' from ' + tableName

        } else {
            SqlUtils.prototype.sqlStr = 'select * from ' + tableName
        }
        return this
    }
    this.keys = []
    this.values = []
    this.whereStr = ""
    this.addKeyStr = ""
    this.addValueStr = ""
    this.updateKeyStr = ""
    this.updateValue = []
    this.doneWhere = ""
    this.where = function (wheres) { //优化 or not and
        for (var key in wheres) {
            this.keys.push(key + " = ? and")
            this.values.push(wheres[key])
        }

        for (var i = 0; i < this.keys.length; i++) {
            if (i == this.keys.length - 1) {
                this.keys[i] = this.keys[i].substring(0, this.keys[i].length - 3)

            }
            this.whereStr += this.keys[i]+" "
        }
        this.sqlStr += ' where ' + this.whereStr


        return this

    }
    this.addValues = function (addValues) {
        this.doneWhere = 'a'
        if (Object.prototype.toString.call(addValues) === '[object Object]') {
            this.addKeyStr = ' ('
            this.addValueStr = ' values ('
            for (var key in addValues) {
                this.addKeyStr += key + ','
                this.addValueStr += "?,"
                this.values.push(addValues[key])
            }
            this.addKeyStr += ')'
            this.addValueStr += ')'
            this.addKeyStr = this.addKeyStr.replace(',)', ')')
            this.addValueStr = this.addValueStr.replace(',)', ')')
        } else if (Array.isArray(addValues)) {
            this.addValueStr = ' values ('
            for (var key in addValues) {
                this.addValueStr += "?,"
                this.values.push(addValues[key])
            }
            this.addValueStr += ')'
            this.addValueStr = this.addValueStr.replace(',)', ')')
        }
        return this
    }
    this.updateValues = function (updateValues) {
        this.doneWhere = 'u'
        this.updateKeyStr = ' set '
        for (const key in updateValues) {
            this.updateKeyStr += key + " = ? "
            this.updateValue.push(updateValues[key])
        }
        this.sqlStr += this.updateKeyStr
        return this
    }

    this.done = function (success, fail) {
        if (this.doneWhere === 'a') {
            this.sqlStr = this.sqlStr + this.addKeyStr + this.addValueStr
            console.log(this.sqlStr);
        } else if (this.doneWhere === 'u') {
            for (const key in this.updateValue) {
                this.values.unshift(this.updateValue[key])
            }
            console.log(this.values);
        }
        console.log(this.tableName);
        select(this.sqlStr, this.values, this.tableName).then(ress => {
            success(ress)
        }).catch(err => {
            console.log(err);
            if (err) {
                fail(err)
            }
        })
    }
}





module.exports = {
    select: select,
    SqlUtils: SqlUtils,
}